﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for BookDAL
/// </summary>
public class BookDAL
{
	public BookDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public List<Book> GetBook()
    {
        List<Book> list = new List<Book>();
        try 
	    {	        
		    SqlConnection con = new SqlConnection("Server=.;Database=Demo;User=sa;Password=nhut247");
            string query = "Select * From Books";
            SqlCommand com = new SqlCommand(query,con);
            con.Open();
            SqlDataReader reader = com.ExecuteReader();
            while(reader.Read())
            {
                Book b = new Book();
                b.BookId = reader.GetInt32(0);
                b.Title = reader.GetString(1);
                b.Price = reader.GetInt32(2);
                list.Add(b);
            }
            con.Close();
	    }
	    catch (Exception ex)
	    {
            Console.WriteLine(ex.ToString());
	    }
        return list;
    }

    public Book GetBook(int BookId)
    {
        Book curBook = null;
        try
        {
            SqlConnection con = new SqlConnection("Server=.;Database=Demo;User=sa;Password=nhut247");
            string query = "Select * From Books where BookId = @id";
            SqlCommand com = new SqlCommand(query, con);
            con.Open();
            com.Parameters.Add(new SqlParameter("@id", BookId));
            SqlDataReader reader = com.ExecuteReader();
            if (reader.Read())
            {
                curBook = new Book();
                curBook.BookId = reader.GetInt32(0);
                curBook.Title = reader.GetString(1);
                curBook.Price = reader.GetInt32(2);
            }
            con.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
        return curBook;
    }

    public void Insert(int bookId, string title, int price)
    {
        try
        {
            SqlConnection con = new SqlConnection("Server=.;Database=Demo;User=sa;Password=nhut247");
            string query = "Insert into Books(BookId,Title,Price) values (@id,@title,@price)";
            SqlCommand com = new SqlCommand(query, con);
            con.Open();
            com.Parameters.Add(new SqlParameter("@id", bookId));
            com.Parameters.Add(new SqlParameter("@title", title));
            com.Parameters.Add(new SqlParameter("@price", price));
            com.ExecuteNonQuery();
            con.Close();
        }
        catch (Exception ex) {
            Console.WriteLine(ex.ToString());
        }
    }

    public void Update(int bookId, string title, int price)
    {
        try
        {
            SqlConnection con = new SqlConnection("Server=.;Database=Demo;User=sa;Password=nhut247");
            string query = "Update Books set Title=@title, Price=@price where BookId = @id";
            SqlCommand com = new SqlCommand(query, con);
            con.Open();
            com.Parameters.Add(new SqlParameter("@id", bookId));
            com.Parameters.Add(new SqlParameter("@title", title));
            com.Parameters.Add(new SqlParameter("@price", price));
            com.ExecuteNonQuery();
            con.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
    }

    public void Delete(int bookId)
    {
        try
        {
            SqlConnection con = new SqlConnection("Server=.;Database=Demo;User=sa;Password=nhut247");
            string query = "Delete From Books where BookId = @id";
            SqlCommand com = new SqlCommand(query, con);
            con.Open();
            com.Parameters.Add(new SqlParameter("@id", bookId));
            //com.Parameters.Add(new SqlParameter("@title", title));
            //com.Parameters.Add(new SqlParameter("@price", price));
            com.ExecuteNonQuery();
            con.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
    }
}